package Dm

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strings"
)

func (or TableInfoMeta) ForeignExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[oracleForeignExist]"
	for _, v := range []string{
		//"USER_constraints",
		"all_constraints",
		"dba_constraints",
	} {
		result.BaseResult.Sql = fmt.Sprintf("select count(1) as \"sum\" from %v where CONSTRAINT_TYPE='R' AND  OWNER = '%v' AND CONSTRAINT_NAME='%v'", v, s.TableInfo.Schema, s.TableInfo.Table)
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil {
			continue
		}
		if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
			result.Result = true
		}
		break
	}
	return
}

func (or TableInfoMeta) ForeignName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var f1 any
	var event = "[oracleForeignName]"
	for _, v := range []string{
		//"USER_constraints",
		"all_constraints", "dba_constraints",
	} {
		result.BaseResult.Sql = fmt.Sprintf("select CONSTRAINT_NAME from %v where CONSTRAINT_TYPE='R' AND  OWNER = '%v' %v",
			v, s.TableInfo.Schema, func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" and CONSTRAINT_NAME like '%%%v%%'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" and CONSTRAINT_NAME = '%v'", s.TableInfo.Table)
				}
			}())

		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.StringGarth,
		}); err != nil || f1 == nil {
			continue
		}
		result.Result = f1.([]string)
		break
	}
	return
}
func foreignPrintInfo(x *global.ForeignResultDefinition) (res string) {
	/*
		ALTER TABLE child_table
		ADD CONSTRAINT fk_parent
		FOREIGN KEY (parent_id) REFERENCES parent_table(id);
	*/
	return fmt.Sprintf("ALTER TABLE \"%v\".\"%v\" \n"+
		"ADD CONSTRAINT \"%v\" \n"+
		"FOREIGN KEY (\"%v\") "+
		"REFERENCES \"%v\".\"%v\"(\"%v\") \n"+
		";", x.Schema, x.TableName,
		x.ForeignName, strings.Join(x.ForeignKey, "\",\""),
		x.RSchema, x.RTableName, strings.Join(x.RColumn, "\",\""),
	)
}
func ConstraintTable(s global.SchemaObjectInput, schema, name string) (table string, res []string, err error) {
	var f1 any
	var event = "[oracleConstraintTable]"
	for _, v := range []string{
		//"USER_cons_columns",
		"all_cons_columns",
		"dba_cons_columns",
	} {
		sqlStr := fmt.Sprintf("SELECT\n"+
			"\t\"TABLE_NAME\" AS \"tableName\",\n"+
			"\t\"COLUMN_NAME\" AS \"columnName\"\n"+
			"FROM\n"+
			"\t%v \n"+
			"WHERE\n"+
			"\t1= 1 \n"+
			"\tAND OWNER = '%v' \n"+
			"\tAND CONSTRAINT_NAME = '%v' \n"+
			"ORDER BY\n"+
			"\tPOSITION", v, schema, name)
		if _, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: sqlStr,
			Expect:       ETL.SMap,
		}); err != nil || f1 == nil {
			continue
		}
		for _, p := range f1.([]map[string]any) {
			if x, ok := p["tableName"]; ok {
				table = fmt.Sprintf("%v", x)
			}
			res = append(res, fmt.Sprintf("%v", p["columnName"]))
		}
		break
	}
	return
}
func constraintsColumn(s global.SchemaObjectInput) (res []string, err error) {
	var (
		f1    any
		event = "[oracleConstraintsColumn]"
	)
	for _, v := range []string{
		//"USER_cons_columns",
		"all_cons_columns",
		"dba_cons_columns",
	} {
		sqlStr := fmt.Sprintf("select \n"+
			"\t\"COLUMN_NAME\" AS \"column\" \n"+
			"\tfrom \n"+
			"\t%v where 1 = 1 \n"+
			"\tAND OWNER='%v' \n"+
			"\tAND CONSTRAINT_NAME = '%v'", v, s.Schema, s.Table)
		if _, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: sqlStr,
			Expect:       ETL.SMap,
		}); err != nil || f1 == nil {
			continue
		}
		for _, p := range f1.([]map[string]any) {
			res = append(res, fmt.Sprintf("%v", p["column"]))
		}
		break
	}

	return
}
func foreignDefinitionConvert(x global.ForeignResultDefinition) (convert global.ForeignDefinitionConvertS) {
	convert = global.ForeignDefinitionConvertS{
		Schema:      x.Schema,
		RSchema:     x.RSchema,
		TableName:   x.TableName,
		RTableName:  x.RTableName,
		ForeignName: x.ForeignName,
		RName:       x.RName,
		ForeignKey:  x.ForeignKey,
		RColumn:     x.RColumn,
		Type:        x.Type,
		PrintInfo:   x.PrintInfo,
	}
	return
}
func (or SchemaObjectMeta) Foreign(s global.SchemaObjectInput) (result global.SchemaObjectReturnResult, err error) {
	var (
		f1    any
		x     global.ForeignResultDefinition
		event = "[oracleForeign]"
	)
	for _, v := range []string{
		//"USER_constraints",
		"all_constraints",
		"dba_constraints",
	} {
		result.BaseResult.Sql = fmt.Sprintf("SELECT\n"+
			"\t\"OWNER\" AS \"schema\",\n"+
			"\t\"TABLE_NAME\" AS \"table\",\n"+
			"\t\"CONSTRAINT_NAME\" AS \"name\",\n"+
			"\t\"R_OWNER\" AS \"rSchema\",\n"+
			"\t\"R_CONSTRAINT_NAME\" AS \"rName\","+
			"\t\"STATUS\" AS \"status\"\n"+
			"FROM\n\t%v \n"+
			"WHERE 1= 1 \n"+
			"\tAND CONSTRAINT_TYPE = 'R' \n"+
			"\tAND OWNER = '%v' \n"+
			"\tAND CONSTRAINT_NAME = '%v'", v, s.Schema, s.Table)
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil {
			continue
		}
		if err = global.MapSubToStructSubJsonName(mapNullActive(f1.(map[string]any)), &x); err != nil {
			return
		}
		x.Type = "foreign"
		break
	}
	if x.RTableName, x.RColumn, err = ConstraintTable(s, x.RSchema, x.RName); err != nil {
		return
	}
	if x.ForeignKey, err = constraintsColumn(s); err != nil {
		return
	}
	x.PrintInfo = foreignPrintInfo(&x)
	result.ForeignResult.Definition = x
	result.ForeignResult.Convert = foreignDefinitionConvert(x)
	return
}
